package ro.imc.ums.dani.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ro.imc.ums.dani.classes.Student;

public class StudentCrud implements Crud {
	private Connection conn = null;

	public StudentCrud() {

	}

	@Override
	public void insert(Object o) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/ums", "root", "");
			Statement sqlState = conn.createStatement();

			Student student = (Student) o;

			String insert = "insert into students"
					+ " (FirstName, LastName, StudentId, Cnp, StudentNumber, Status)"
					+ " values " + "('" + student.getFirstName() + "','"
					+ student.getLastName() + "','" + student.getId() + "','"
					+ student.getCnp() + "','" + student.getStudentNo() + "','"
					+ student.isStatus() + " ') ";

			sqlState.executeUpdate(insert);
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

	}

	@Override
	public ArrayList<Object> select() {
		ArrayList<Object> studentList = new ArrayList<Object>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/ums", "root", "");
			Statement sqlState = conn.createStatement();
			String select = "select * from students";
			ResultSet result = sqlState.executeQuery(select);
			while (result.next()) {
				Student student = new Student(result.getString(2),
						result.getString(3), Integer.parseInt(result
								.getString(4)), result.getString(5),
						Integer.parseInt(result.getString(6)),
						Boolean.parseBoolean(result.getString(7)));
				studentList.add(student);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return studentList;
	}

	@Override
	public void update() {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete() {
		// TODO Auto-generated method stub

	}
	
	public ArrayList<Object> selectOne(String cnp){
		ArrayList<Object> studentList = new ArrayList<Object>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/ums", "root", "");
			Statement sqlState = conn.createStatement();
			String select = "select * from students where cnp =" + cnp;
			ResultSet result = sqlState.executeQuery(select);
			while (result.next()) {
				Student student = new Student(result.getString(2),
						result.getString(3), Integer.parseInt(result
								.getString(4)), result.getString(5),
						Integer.parseInt(result.getString(6)),
						Boolean.parseBoolean(result.getString(7)));
				studentList.add(student);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return studentList;
	}
	

}
